package cn.com.do1.component.merchant.merchant.dao.impl;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import cn.com.do1.common.dac.Pager;
import cn.com.do1.common.exception.BaseException;
import cn.com.do1.common.framebase.dqdp.BaseDAOImpl;
import cn.com.do1.common.framebase.dqdp.IBaseDBVO;
import cn.com.do1.common.util.AssertUtil;
import cn.com.do1.component.merchant.merchant.dao.IMerchantDAO;
import cn.com.do1.component.merchant.merchant.vo.TbCzMerchantDetailVO;
import cn.com.do1.component.merchant.merchant.vo.TbCzMerchantListVO;
import cn.com.do1.component.merchant.merchant.vo.TbCzMerchantVO;
import cn.com.do1.component.systemmgr.org.model.TbDqdpOrgPO;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Copyright &copy; 2010 广州市道一信息技术有限公司 All rights reserved. User: ${user}
 */
public class MerchantDAOImpl extends BaseDAOImpl implements IMerchantDAO {

    private final static transient Logger logger = LoggerFactory.getLogger(MerchantDAOImpl.class);

    @Override
    public Pager searchMerchant(Map searchMap, Pager pager) throws Exception, BaseException {

        String dataSql =
                "select m.*,person.PERSON_NAME  from tb_cz_merchant m LEFT JOIN tb_dqdp_person person on m.PERSON_ID = person.PERSON_ID";
        String countSql = "select count(1) from tb_cz_merchant m LEFT JOIN tb_dqdp_person person on m.PERSON_ID = person.PERSON_ID";

        return super.pageSearchByField(TbCzMerchantVO.class, countSql, dataSql, searchMap, pager);

    }

    public TbCzMerchantVO getMerchantByPersonId(String PERSON_ID) throws SQLException {
        super.preparedSql("SELECT m.* FROM tb_cz_merchant m LEFT JOIN tb_person_user_ref person ON person.USER_ID = m.USER_ID where person.PERSON_ID=:PERSON_ID");
        super.setPreValue("PERSON_ID", PERSON_ID);
        return super.executeQuery(TbCzMerchantVO.class);
    }

    public TbCzMerchantVO getMerchantByUserID(String UserID) throws SQLException {
        super.preparedSql("SELECT * FROM tb_cz_merchant where User_ID =:UserID");
        super.setPreValue("UserID", UserID);
        return super.executeQuery(TbCzMerchantVO.class);
    }
    
    /**
     * 根据用户名查看商家信息
     * 
     * @param PersonId
     * @return
     * @throws SQLException
     */
    public TbCzMerchantVO getMerchantByUserAccount(String UserAccount) throws SQLException{
        super.preparedSql("SELECT m.*,us.PASSWORD,us.USER_NAME  FROM tb_cz_merchant m  join tb_dqdp_user us on m.USER_ID = us.USER_ID where us.USER_NAME=:USER_NAME");
        super.setPreValue("USER_NAME", UserAccount);
        return super.executeQuery(TbCzMerchantVO.class);
    }

    public TbCzMerchantDetailVO getMerchantDetailByPersonId(String PERSON_ID) throws SQLException {
        super.preparedSql("SELECT m.* FROM tb_cz_merchant m LEFT JOIN tb_person_user_ref person ON person.USER_ID = m.USER_ID where person.PERSON_ID=:PERSON_ID");
        super.setPreValue("PERSON_ID", PERSON_ID);
        return super.executeQuery(TbCzMerchantDetailVO.class);
    }

    @Override
    public void delMerchantByPersonId(String PersonId) throws SQLException {

        super.preparedSql("DELETE FROM tb_cz_merchant WHERE user_id IN ( SELECT USER_ID FROM tb_person_user_ref WHERE PERSON_ID = :PERSON_ID)");
        super.setPreValue("PERSON_ID", PersonId);
        super.executeUpdate();
    }

    public Pager listPersonByOrg(String orgId, Pager pager, Map searchValue) throws Exception, BaseException {
        TbDqdpOrgPO orgPO = (TbDqdpOrgPO) searchByPk(TbDqdpOrgPO.class, orgId);

        if (!AssertUtil.isEmpty(orgPO)) {
            searchValue.put("leftvalue", orgPO.getLeftvalue());
            searchValue.put("rightvalue", orgPO.getRightvalue());
        }

        StringBuffer dataSql = new StringBuffer();
        dataSql.append("SELECT o.leftvalue, ");
        dataSql.append("       u.user_id, ");
        dataSql.append("       u.user_name, ");
        dataSql.append("       u. STATUS, ");
        dataSql.append("       p.person_id, ");
        dataSql.append("       p.person_name, ");
        dataSql.append("       p.age, ");
        dataSql.append("       p.sex, ");
        dataSql.append("       o.organization_name AS org_name, ");
        dataSql.append("       o.organization_id   AS org_id, ");
        dataSql.append("       m.ID, ");
        dataSql.append("       m. NAME, ");
        dataSql.append("       m.pay_Password, ");
        dataSql.append("       m.money, ");
        dataSql.append("       m.discount, ");
        dataSql.append("       m.mobile, ");
        dataSql.append("       m.qq, ");
        dataSql.append("       m.oper, ");
        dataSql.append("       m.INSERT_DATE, ");
        dataSql.append("       m.UPDATE_DATE, ");
        dataSql.append("       m.is_client ");
        dataSql.append("FROM   tb_dqdp_user u, ");
        dataSql.append("       tb_person_user_ref pur, ");
        dataSql.append("       tb_person_organization_ref por, ");
        dataSql.append("       tb_dqdp_organization o, ");
        dataSql.append("       tb_dqdp_person p, ");
        dataSql.append("       tb_cz_merchant m ");
        dataSql.append("WHERE  u.user_id = :user_id ");
        dataSql.append("AND  u.user_id = pur.user_id ");
        dataSql.append("       AND p.person_id = pur.person_id ");
        dataSql.append("       AND p.person_id = por.person_id ");
        dataSql.append("       AND o.organization_id = por.org_id ");
        dataSql.append("       AND m.USER_ID = u.USER_ID ");
        dataSql.append("       AND u.user_name LIKE :userName ");
        dataSql.append("       AND p.person_name LIKE :personName ");
        dataSql.append("       AND m.MOBILE LIKE :mobile ");
        dataSql.append("       AND u.STATUS = :status ");
        dataSql.append("       AND  m.DISCOUNT = :discount ");
        dataSql.append("       AND m.MONEY >= :startMoney AND m.MONEY <= :endMoney ");
        dataSql.append("       AND m.INSERT_DATE >= :startDate AND m.INSERT_DATE <= :endDate ");
        dataSql.append("       AND por.org_id IN (SELECT organization_id ");
        dataSql.append("                          FROM   tb_dqdp_organization ");
        dataSql.append("                          WHERE  leftvalue >= :leftvalue ");
        dataSql.append("                                 AND rightvalue <= :rightvalue) ");

        String orderBy = " ORDER BY INSERT_DATE desc ";



        String countSQL =
                "select count(1) from (" + dataSql.toString().replaceAll("(?i)order\\s+by\\s+.[^\\s,]+(,\\s+.[^\\s,]+)*", "") + "  ) a ";



        return super.pageSearchByField(TbCzMerchantListVO.class, countSQL, dataSql.toString() + orderBy, searchValue, pager);
    }

    @Override
    public boolean updateMoney(String userId, float money) throws SQLException {
        super.preparedSql("UPDATE tb_cz_merchant SET money = :money WHERE user_id = '1111'");
        super.setPreValue("userId", userId);
        super.setPreValue("money", money);
        return super.executeUpdate() > 0;
    }

    @Override
    public boolean addMoney(String userId, float money) throws SQLException {
        super.preparedSql("UPDATE tb_cz_merchant SET money = money + :money WHERE user_id = :user_id");
        super.setPreValue("user_id", userId);
        super.setPreValue("money", money);
        return super.executeUpdate() > 0;
    }

    @Override
    public boolean minMoney(String userId, float money) throws SQLException {
        super.preparedSql("UPDATE tb_cz_merchant SET money = money - :money WHERE user_id = :user_id");
        super.setPreValue("user_id", userId);
        super.setPreValue("money", money);
        return super.executeUpdate() > 0;
    }
}
